package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import com.oocl.dao.CustomerDao;
import com.oocl.pojo.Customer;
import com.oocl.pojo.Favc;
import com.oocl.util.DBUtil;

public class CustomerDaoImpl implements CustomerDao{

	public int insertOne(Customer c) {
		String sql = "insert into customer(id,uname,sex,salary,birth) values(seq_cust_id.nextval,?,?,?,?)";
		int count=0;
		Connection con = null;
		PreparedStatement stat = null;
		try {
			con  = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setString(1,c.getUname());
			stat.setBoolean(2,c.isSex());
			stat.setDouble(3,c.getSalary());
			stat.setDate(4,new Date(c.getBirth().getTime()));
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, null);
		}
		return count;
	}

	public List<Customer> findAll() {
		String sql = "select * from customer";
		List<Customer> list = new ArrayList<Customer>();
		Connection con = null;
		PreparedStatement stat =null;
		ResultSet rs = null;
        try {
        	con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			rs = stat.executeQuery();
			while(rs.next()){
				Customer c = new Customer();
				c.setId(rs.getInt("id"));
				c.setUname(rs.getString("uname"));
				c.setBirth(rs.getDate("birth"));
				c.setSalary(rs.getDouble("salary"));
				c.setSex(rs.getBoolean("sex"));
				list.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, rs);
			
		}
		return list;
	}

	public int insertOne(Customer c, int[] favcIds) {
		String sql = "insert into customer(id,uname,sex,salary,birth) values(seq_cust_id.nextval,?,?,?,?)";
		int count=0;
		Connection con = null;
		PreparedStatement stat = null;
		ResultSet rs=null;
		try {
			con  = DBUtil.createConnection();
			con.setAutoCommit(false);
			stat = con.prepareStatement(sql);
			stat.setString(1,c.getUname());
			stat.setBoolean(2,c.isSex());
			stat.setDouble(3,c.getSalary());
			stat.setDate(4,new Date(c.getBirth().getTime()));
			count = stat.executeUpdate();
			String sql1 ="select id from customer where uname = '"+c.getUname()+"'";
			PreparedStatement stat1 = con.prepareStatement(sql1);
			rs = stat1.executeQuery();
			int id =-1;
			if(rs.next()){
				
				id= rs.getInt("id");
				System.out.println(id);
			}
			String sql2 = "insert into customer_favc(customer_id,favc_id) values(?,?)";
			PreparedStatement stat2 = con.prepareStatement(sql2);
			
			for(int i=0;i<favcIds.length;i++){
				System.out.println(favcIds[i]);
				stat2.setInt(1, id);
				stat2.setInt(2, favcIds[i]);
				stat2.addBatch();
			}
			stat2.executeBatch();
			con.commit();
		} catch (SQLException e) {
			count = 0;
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, rs);
		}
		return count;
	}

	public Customer load(Integer id) {
		String sql = "select * from customer where id =?";
		Connection con = null;
		PreparedStatement stat =null;
		ResultSet rs = null;
		Customer c = new Customer();
        try {
        	con = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setInt(1, id);
			rs = stat.executeQuery();
			if(rs.next()){
				c.setId(rs.getInt("id"));
				c.setUname(rs.getString("uname"));
				c.setBirth(rs.getDate("birth"));
				c.setSalary(rs.getDouble("salary"));
				c.setSex(rs.getBoolean("sex"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, rs);
			
		}
		return c;
	}

	public int update(Customer c) {
		String sql = "update customer set uname=?,sex=?,salary=?,birth=? where id=?";
		int count=0;
		Connection con = null;
		PreparedStatement stat = null;
		try {
			con  = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setString(1,c.getUname());
			stat.setBoolean(2,c.isSex());
			stat.setDouble(3,c.getSalary());
			stat.setDate(4,new Date(c.getBirth().getTime()));
			stat.setInt(5,c.getId());
			count = stat.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(con, stat, null);
		}
		return count;
	}

	public int delete(int id) {
		String sql = "delete from customer  where id=?";
		int count=0;
		Connection con = null;
		PreparedStatement stat = null;
		try {
		    con  = DBUtil.createConnection();
			stat = con.prepareStatement(sql);
			stat.setInt(1,id);
			count = stat.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}
